﻿using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;

namespace footwear
{
    public class PosterAdsDao
    {
        public List<PosterAds> GetListAds()
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var list = new List<PosterAds>();
            try
            {
                var myCommand = new MySqlCommand("GetListAds;", DbConnect.Connection) { CommandType = CommandType.StoredProcedure };
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    var posterAds = new PosterAds
                                        {
                                            Id = reader["Id"].ToString(),
                                            Title = reader["Title"].ToString(),
                                            TitleEng = reader["TitleEng"].ToString(),
                                            DescriptionEng = reader["DescriptionEng"].ToString(),
                                            Description = reader["Description"].ToString(),
                                            ImageSource = reader["Image"].ToString()
                                        };
                    list.Add(posterAds);
                }
                DbConnect.CloseConnection();
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
            }
            return list;
        }

        public PosterAds GetAdsById(string id)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var item = new PosterAds();
            try
            {
                string query = "SELECT Id, Title, Description, TitleEng, DescriptionEng, Image FROM poster_ads WHERE Id=?ItemId";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                myCommand.Parameters.AddWithValue("ItemId", id);
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    item.Id = reader["Id"].ToString();
                    item.Title = reader["Title"].ToString();
                    item.Description = reader["Description"].ToString();
                    item.TitleEng = reader["TitleEng"].ToString();
                    item.DescriptionEng = reader["DescriptionEng"].ToString();
                    item.ImageSource = reader["Image"].ToString();
                }
                DbConnect.CloseConnection();
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
            }
            return item;
        }

        public bool UpdateAdsById(string id, string title, string description, string imagelink)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            try
            {
                string query = "UPDATE poster_ads SET Title=?title, Description=?description, Image=?imagelink WHERE Id=?id";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                myCommand.Parameters.AddWithValue("id", id);
                myCommand.Parameters.AddWithValue("title", title);
                myCommand.Parameters.AddWithValue("description", description);
                myCommand.Parameters.AddWithValue("imagelink", imagelink);
                try
                {
                    myCommand.ExecuteNonQuery();
                    DbConnect.CloseConnection();
                    return true;
                }
                catch
                {
                    DbConnect.CloseConnection();
                    return false;
                }
            }
            catch (MySqlException)
            {
                return false;
            }
        }

        public bool AddPosterAds(string title, string description, string imageSource)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            try
            {
                var myCommand = new MySqlCommand("AddPosterAds;", DbConnect.Connection) { CommandType = CommandType.StoredProcedure };
                myCommand.Parameters.AddWithValue("?title", title);
                myCommand.Parameters["?title"].Direction = ParameterDirection.Input;
                myCommand.Parameters.AddWithValue("?description", description);
                myCommand.Parameters["?description"].Direction = ParameterDirection.Input;
                myCommand.Parameters.AddWithValue("?imageSource", imageSource);
                myCommand.Parameters["?imageSource"].Direction = ParameterDirection.Input;
                myCommand.ExecuteNonQuery();
                DbConnect.CloseConnection();
                return true;
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
                return false;
            }
        }
    }
}